package com.nord.Services;

import java.util.HashMap;
import java.util.List;

import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class SQLiteAdapter {
	public static final int DATABASE_VERSION = 1;

	public static final String DATABASE_NAME = "DATABASE";
	public static final String DATABASE_TABLE_COOKIES = "cookies";
	public static final String DATABASE_TABLE_NEWS = "news";
	public static final String DATABASE_TABLE_PHONE_BOOK = "phonebook";
	public static final String DATABASE_TABLE_EVENTS = "events";
	public static final String DATABASE_TABLE_USER = "user";
	/* Cookies */
	public static final String KEY_CONTENT_version = "version";
	public static final String KEY_CONTENT_name = "name";
	public static final String KEY_CONTENT_value = "value";
	public static final String KEY_CONTENT_domain = "domain";
	public static final String KEY_CONTENT_path = "path";
	public static final String KEY_CONTENT_expdate = "expdate";
	public static final String KEY_CONTENT_username = "username";

	/* News */
	public static final String KEY_CONTENT_title = "title";
	public static final String KEY_CONTENT_body = "body";
	public static final String KEY_CONTENT_date = "date";
	public static final String KEY_CONTENT_id = "id";
	public static final String KEY_CONTENT_translation = "translation";
	public static final String KEY_CONTENT_likers = "likers";
	public static final String KEY_CONTENT_lovers = "lovers";
	public static final String KEY_CONTENT_comments = "comments";
	public static final String KEY_CONTENT_author = "author";
	public static final String KEY_CONTENT_slug = "slug";
	
	/* Phonebook */
	public static final String KEY_CONTENT_fullname = "fullname";
	public static final String KEY_CONTENT_phonenumber = "phonenumber";
	public static final String KEY_CONTENT_email = "email";

	/* Events */
	public static final String KEY_CONTENT_eventname = "eventname";
	public static final String KEY_CONTENT_eventdate = "eventdate";
	public static final String KEY_CONTENT_max = "max";
	public static final String KEY_CONTENT_checkbox = "checkbox";
	public static final String KEY_CONTENT_position = "position";

	/* User*/
	public static final String KEY_CONTENT_user_name = "username";
	public static final String KEY_CONTENT_full_name = "fullname";
	
	private static final String SCRIPT_CREATE_TABLE_COOKIE = "CREATE TABLE "
			+ DATABASE_TABLE_COOKIES + " (" 
			+ KEY_CONTENT_version + " INTEGER,"
			+ KEY_CONTENT_name + " TEXT," 
			+ KEY_CONTENT_value + " TEXT,"
			+ KEY_CONTENT_domain + " TEXT," 
			+ KEY_CONTENT_path + " TEXT,"
			+ KEY_CONTENT_expdate + " TEXT," 
			+ KEY_CONTENT_username + " TEXT);";

	private static final String SCRIPT_CREATE_TABLE_NEWS = "CREATE TABLE "
			+ DATABASE_TABLE_NEWS + " (" 
			+ KEY_CONTENT_title + " TEXT,"
			+ KEY_CONTENT_body + " TEXT," 
			+ KEY_CONTENT_date + " TEXT,"
			+ KEY_CONTENT_id + " TEXT,"
			+ KEY_CONTENT_translation + " TEXT,"
			+ KEY_CONTENT_likers + " TEXT," 
			+ KEY_CONTENT_lovers + " TEXT,"
			+ KEY_CONTENT_comments + " TEXT,"
			+ KEY_CONTENT_author + " TEXT, " 
			+ KEY_CONTENT_slug + " TEXT);";

	private static final String SCRIPT_CREATE_TABLE_PHONE_BOOK = "CREATE TABLE "
			+ DATABASE_TABLE_PHONE_BOOK + " ("
			+ KEY_CONTENT_fullname + " TEXT,"
			+ KEY_CONTENT_email + " TEXT,"
			+ KEY_CONTENT_phonenumber + " TEXT);";

	private static final String SCRIPT_CREATE_TABLE_EVENTS = " CREATE TABLE "
			+ DATABASE_TABLE_EVENTS + " (" 
			+ KEY_CONTENT_eventname + " TEXT,"
			+ KEY_CONTENT_eventdate + " TEXT," 
			+ KEY_CONTENT_max + " TEXT,"
			+ KEY_CONTENT_checkbox + " TEXT,"
			+ KEY_CONTENT_position + " TEXT);";
	
	private static final String SCRIPT_CREATE_TABLE_USER = " CREATE TABLE "
			+ DATABASE_TABLE_USER + " ("
			+ KEY_CONTENT_user_name + " TEXT,"
			+ KEY_CONTENT_full_name + " TEXT);";

	private SQLiteHelper sqLiteHelper;
	private SQLiteDatabase sqLiteDatabase;

	private Context context;

	/*
	 * Data invariant: - KEY_CONTENT string constant represent column names in
	 * the database - DATABASE_TABLE string constant represent database table
	 * names - SCRIPT_CREATE_DATABASE string constant represent SQL query to
	 * create a table - DATABASE_VERSION integer constant represent current
	 * version of our database - sqLiteDatabase is a SQLiteDatabase object -
	 * sqLiteHelper is a SQLiteHelper object
	 */

	/*
	 * Usage: SQLiteAdapter sqlAdapter = new SQLiteAdapter(Context); Post : A
	 * new SQLiteAdapter instance is available
	 */
	public SQLiteAdapter(Context c) {
		context = c;
	}

	/*
	 * Usage: sqlAdapter.open(String); Pre : An SQLiteAdapter instance exists
	 * Post : The SQLite database is open for reading or writing, depending on
	 * the argument
	 */
	public SQLiteAdapter open(String operation) {
		if (operation.matches("R")) {
			sqLiteHelper = new SQLiteHelper(context, DATABASE_NAME, null,
					DATABASE_VERSION);
			sqLiteDatabase = sqLiteHelper.getReadableDatabase();
		} else {
			sqLiteHelper = new SQLiteHelper(context, DATABASE_NAME, null,
					DATABASE_VERSION);
			sqLiteDatabase = sqLiteHelper.getWritableDatabase();
		}
		return this;
	}

	public void logout() {
		sqLiteDatabase.delete(DATABASE_TABLE_USER, null, null);
		sqLiteDatabase.delete(DATABASE_TABLE_COOKIES, null, null);
		sqLiteDatabase.delete(DATABASE_TABLE_NEWS, null, null);
		sqLiteDatabase.delete(DATABASE_TABLE_PHONE_BOOK, null, null);
		sqLiteDatabase.delete(DATABASE_TABLE_EVENTS, null, null);
	}

	/*
	 * Usage: sqlAdapter.close(void); Pre : An SQLiteAdapter instance exists
	 * Post : The SQLite database is closed No further use is possible
	 */
	public void close() {
		sqLiteHelper.close();
		sqLiteDatabase.close();
	}

	public HashMap<String, String> updateLocation(String resource, List<HashMap<String, String>> events) {
		HashMap<String,String> event = new HashMap<String,String>();
		try{
			JSONObject json = new JSONObject(resource);
			for(int i = 0;i < json.length();i++){
				String position = json.get(events.get(i).get("title")).toString();
				event.put(events.get(i).get("title"), position);
				if(!position.contains("[]")){
					String query = "UPDATE "
							+ DATABASE_TABLE_EVENTS
							+ " SET "
							+ KEY_CONTENT_position
							+ " = '"
							+ position
							+ "' WHERE "
							+ KEY_CONTENT_eventname
							+ " = '"
							+ events.get(i).get("title").toString() + "'";
					sqLiteDatabase.execSQL(query);
				}
			}
		} catch (Exception e){}
		return event;

	}
	
	public void insert(String table, ContentValues content){
		sqLiteDatabase.insert(table, null, content);
	}
	

/*
	 * Usage: sqlAdapter.deleteAll(String); Pre : An SQLiteAdapter instance
	 * exists Post : Performs an SQL DELETE operation on a table specified with
	 * the argument. This function deletes all data in the table
	 */
	public void deleteAll(String table) {
		sqLiteDatabase.delete(table,null,null);
	}

	/*
	 * Usage: sqlAdapter.queryEvents(void); Pre : An SQLiteAdapter instance
	 * exists Post : Performs an SQL SELECT operation on the events table
	 * returns the data in an Cursor object
	 */
	public Cursor queryEvents() {
		Cursor cursor = sqLiteDatabase.rawQuery(
				"SELECT eventname,eventdate,url,position FROM "
						+ DATABASE_TABLE_EVENTS, null);
		return cursor;
	}

	/*
	 * Usage: sqlAdapter.loadPhoneBook(void); Pre : An SQLiteAdapter instance
	 * exists Post : Performs an SQL SELECT operation on the Phonebook table
	 * returns the data in an Cursor object
	 */
	public Cursor queryPhoneBook() {
		Cursor cursor = sqLiteDatabase.rawQuery(
				"SELECT rowid as _id, fullname,email,phonenumber FROM "
						+ DATABASE_TABLE_PHONE_BOOK, null);
		return cursor;
	}


	/*
	 * Usage: sqlAdapter.queryNews(void); Pre : An SQLiteAdapter instance exists
	 * Post : Performs an SQL SELECT operation on the Phonebook table returns
	 * the data in an Cursor object
	 */
	
	public Cursor queryTable(String table,String[] columns) {
		Cursor cursor = sqLiteDatabase.query(table, columns,
				null, null, null, null, null);
		return cursor;
	}

	
	public class SQLiteHelper extends SQLiteOpenHelper {
		/*
		 * Usage: SQLiteHelper sqLiteHelper = new
		 * SQLiteHelper(Context,String,CursorFactory,int); Post : A new
		 * SQLiteHelper instance is ready
		 */
		public SQLiteHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		/*
		 * Usage: Do not call explicitly Post : New tables are created
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(SCRIPT_CREATE_TABLE_COOKIE);
			db.execSQL(SCRIPT_CREATE_TABLE_NEWS);
			db.execSQL(SCRIPT_CREATE_TABLE_PHONE_BOOK);
			db.execSQL(SCRIPT_CREATE_TABLE_EVENTS);
			db.execSQL(SCRIPT_CREATE_TABLE_USER);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}

	}

}